import { getDatabase } from './init'
import bcrypt from 'bcryptjs'

// 创建用户相关表
export async function createUserTables(): Promise<void> {
  const client = await getDatabase().connect()
  
  try {
    console.log('🔄 创建用户相关表...')
    
    // 创建用户表
    await client.query(`
      CREATE TABLE IF NOT EXISTS users (
        id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
        email TEXT UNIQUE,
        phone TEXT UNIQUE,
        password_hash TEXT,
        username TEXT,
        avatar_url TEXT,
        is_verified BOOLEAN DEFAULT FALSE,
        login_type TEXT NOT NULL CHECK (login_type IN ('email', 'phone', 'oauth')),
        oauth_provider TEXT,
        oauth_id TEXT,
        created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        last_login_at TIMESTAMP WITH TIME ZONE,
        
        -- 确保至少有一种登录方式
        CONSTRAINT user_login_method CHECK (
          (email IS NOT NULL AND password_hash IS NOT NULL) OR 
          (phone IS NOT NULL) OR 
          (oauth_provider IS NOT NULL AND oauth_id IS NOT NULL)
        )
      )
    `)
    
    // 创建验证码表
    await client.query(`
      CREATE TABLE IF NOT EXISTS verification_codes (
        id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
        type TEXT NOT NULL CHECK (type IN ('phone_login', 'phone_register', 'email_verify', 'password_reset')),
        recipient TEXT NOT NULL, -- 手机号或邮箱
        code TEXT NOT NULL,
        expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
        is_used BOOLEAN DEFAULT FALSE,
        attempts INTEGER DEFAULT 0,
        created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        
        -- 防止重复发送，同一recipient+type在短时间内只能有一个有效验证码
        UNIQUE(recipient, type, expires_at)
      )
    `)
    
    // 创建用户会话表
    await client.query(`
      CREATE TABLE IF NOT EXISTS user_sessions (
        id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
        user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        token_hash TEXT NOT NULL,
        device_info TEXT,
        ip_address TEXT,
        expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
        created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        last_used_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
      )
    `)
    
    // 创建索引
    await client.query(`CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_users_phone ON users(phone)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_verification_codes_recipient ON verification_codes(recipient, type)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_verification_codes_expires ON verification_codes(expires_at)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_user_sessions_user_id ON user_sessions(user_id)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_user_sessions_expires ON user_sessions(expires_at)`)
    
    console.log('✅ 用户表创建完成')
    
    // 创建默认管理员用户（如果不存在）
    const adminExists = await client.query(`
      SELECT id FROM users WHERE email = 'admin@aide.com'
    `)
    
    if (adminExists.rows.length === 0) {
      const hashedPassword = await bcrypt.hash('admin123456', 12)
      await client.query(`
        INSERT INTO users (email, password_hash, username, login_type, is_verified)
        VALUES ('admin@aide.com', $1, '管理员', 'email', TRUE)
      `, [hashedPassword])
      
      console.log('✅ 创建默认管理员用户: admin@aide.com / admin123456')
    }
    
    console.log('🎉 用户系统数据库初始化完成')
    
  } catch (error) {
    console.error('❌ 用户表创建失败:', error)
    throw error
  } finally {
    client.release()
  }
}

// 清理过期数据
export async function cleanupExpiredData(): Promise<void> {
  const client = await getDatabase().connect()
  
  try {
    // 清理过期验证码
    await client.query(`
      DELETE FROM verification_codes 
      WHERE expires_at < NOW()
    `)
    
    // 清理过期会话
    await client.query(`
      DELETE FROM user_sessions 
      WHERE expires_at < NOW()
    `)
    
    console.log('✅ 过期数据清理完成')
  } catch (error) {
    console.error('❌ 数据清理失败:', error)
  } finally {
    client.release()
  }
}

// 如果直接运行此文件，执行迁移
if (require.main === module) {
  createUserTables()
    .then(() => process.exit(0))
    .catch((error) => {
      console.error(error)
      process.exit(1)
    })
}
